using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using eShop.Model;
using eShop.Utility;

namespace eShop.DataAccess
{
    public partial class ProductReviewDAO : DatabaseUtility
    {
        public static Int32 CreateNewTblProductReview
        (
            Int32 productId,
            String customerName,
            String customerEmail,
            Decimal? ratingValue,
            String reviewText,
            DateTime? reviewDate,
            Boolean isApproved
        )
        {
            //creating an empty TblProductReview object
            ProductReview tblProductReview = new ProductReview();
            tblProductReview.ProductId = productId;
            tblProductReview.CustomerName = customerName;
            tblProductReview.CustomerEmail = customerEmail;
            tblProductReview.RatingValue = ratingValue;
            tblProductReview.ReviewText = reviewText;
            tblProductReview.ReviewDate = reviewDate;
            tblProductReview.IsApproved = isApproved;

            return ProductReviewDAO.CreateNewTblProductReview(tblProductReview);
        }

        public static bool UpdateTblProductReview
        (
            Int32 productReviewId,
            Int32 productId,
            String customerName,
            String customerEmail,
            Decimal? ratingValue,
            String reviewText,
            DateTime? reviewDate,
            Boolean isApproved
        )
        {
            ProductReview tblProductReview = ProductReviewDAO.GetTblProductReviewByProductReviewId(productReviewId);

            tblProductReview.ProductId = productId;
            tblProductReview.CustomerName = customerName;
            tblProductReview.CustomerEmail = customerEmail;
            tblProductReview.RatingValue = ratingValue;
            tblProductReview.ReviewText = reviewText;
            tblProductReview.ReviewDate = reviewDate;
            tblProductReview.IsApproved = isApproved;

            return ProductReviewDAO.UpdateTblProductReview(tblProductReview);
        }

        public static bool DeleteTblProductReview
        (
            Int32 productReviewId
        )
        {
            //Validate Input
            if (productReviewId <= GetProductReviewIdMinValue)
                throw (new ArgumentOutOfRangeException("productReviewId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ProductReviewId", SqlDbType.Int, 0, ParameterDirection.Input, productReviewId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_DELETETBLPRODUCTREVIEW);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        public static bool DeleteTblProductReviewList(System.Collections.Generic.List<Int32> productReviewIdListToDelete)
        {
            //Validate Input
            foreach (Int32 productReviewId in productReviewIdListToDelete)
                if (productReviewId <= GetProductReviewIdMinValue)
                    throw (new ArgumentOutOfRangeException("productReviewId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(productReviewIdListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ProductReviewIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_DELETETBLPRODUCTREVIEWLIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == productReviewIdListToDelete.Count ? true : false);
        }

        public static ProductReview GetTblProductReviewByProductReviewId(Int32 productReviewId)
        {
            if (productReviewId <= GetProductReviewIdMinValue)
                throw (new ArgumentOutOfRangeException("productReviewId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductReviewId", SqlDbType.Int, 0, ParameterDirection.Input, productReviewId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_GETTBLPRODUCTREVIEWBYPRODUCTREVIEWID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductReviewCollectionFromReader);
            List<ProductReview> objCollection = ((List<ProductReview>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static List<ProductReview> GetTblProductReviewByProductId(Int32 productId)
        {
            if (productId <= GetProductIdMinValue)
                throw (new ArgumentOutOfRangeException("productId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, productId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_GETTBLPRODUCTREVIEWBYPRODUCTID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductReviewCollectionFromReader);
            List<ProductReview> objCollection = ((List<ProductReview>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection;
            else
                return null;
        }

        public static List<ProductReview> GetTblProductReviewByProductIdPaged(Int32 productId, String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (productId <= GetProductIdMinValue)
                throw (new ArgumentOutOfRangeException("productId"));

            if (orderBy == "")
                orderBy = "ProductId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, productId);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_GETTBLPRODUCTREVIEWBYPRODUCTIDPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductReviewCollectionFromReader);
            List<ProductReview> objCollection = ((List<ProductReview>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        public static int GetTblProductReviewByProductIdPagedCount(Int32 productId, String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (productId <= GetProductIdMinValue)
                throw (new ArgumentOutOfRangeException("productId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, productId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_GETTBLPRODUCTREVIEWBYPRODUCTIDPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static List<ProductReview> GetAllTblProductReview()
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_GETALLTBLPRODUCTREVIEW);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductReviewCollectionFromReader);
            List<ProductReview> objCollection = ((List<ProductReview>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        public static List<ProductReview> GetAllTblProductReviewPaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "ProductReviewId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_GETALLTBLPRODUCTREVIEWPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductReviewCollectionFromReader);
            List<ProductReview> objCollection = ((List<ProductReview>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllTblProductReviewPagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_GETALLTBLPRODUCTREVIEWPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static bool Save(ProductReview productReview)
        {
            if (productReview.ProductReviewId <= GetProductReviewIdMinValue)
            {
                Int32 TempId = CreateNewTblProductReview(productReview);
                if (TempId > 0)
                {
                    productReview.ProductReviewId = TempId;
                    return true;
                }
                else
                {
                    // error occurs
                    return false;
                }
            }
            else
            {	//This is a update Command
                return (UpdateTblProductReview(productReview));
            }

        }

        public static Int32 CreateNewTblProductReview(ProductReview newTblProductReview)
        {
            // validate Parameters
            if (newTblProductReview == null)
                throw (new ArgumentNullException("newTblProductReview"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductReview.ProductId);
            if (newTblProductReview.CustomerName != null)
                AddParameterToSqlCmd(sqlCmd, "@CustomerName", SqlDbType.NVarChar, 100, ParameterDirection.Input, newTblProductReview.CustomerName);
            else
                AddParameterToSqlCmd(sqlCmd, "@CustomerName", SqlDbType.NVarChar, 100, ParameterDirection.Input, System.DBNull.Value);
            if (newTblProductReview.CustomerEmail != null)
                AddParameterToSqlCmd(sqlCmd, "@CustomerEmail", SqlDbType.NVarChar, 100, ParameterDirection.Input, newTblProductReview.CustomerEmail);
            else
                AddParameterToSqlCmd(sqlCmd, "@CustomerEmail", SqlDbType.NVarChar, 100, ParameterDirection.Input, System.DBNull.Value);
            if (newTblProductReview.RatingValue != null)
                AddParameterToSqlCmd(sqlCmd, "@RatingValue", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProductReview.RatingValue);
            else
                AddParameterToSqlCmd(sqlCmd, "@RatingValue", SqlDbType.Decimal, 9, ParameterDirection.Input, System.DBNull.Value);
            if (newTblProductReview.ReviewText != null)
                AddParameterToSqlCmd(sqlCmd, "@ReviewText", SqlDbType.NVarChar, -1, ParameterDirection.Input, newTblProductReview.ReviewText);
            else
                AddParameterToSqlCmd(sqlCmd, "@ReviewText", SqlDbType.NVarChar, -1, ParameterDirection.Input, System.DBNull.Value);
            if (newTblProductReview.ReviewDate != null)
                AddParameterToSqlCmd(sqlCmd, "@ReviewDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newTblProductReview.ReviewDate);
            else
                AddParameterToSqlCmd(sqlCmd, "@ReviewDate", SqlDbType.DateTime, 0, ParameterDirection.Input, System.DBNull.Value);
            if (newTblProductReview.IsApproved != null)
                AddParameterToSqlCmd(sqlCmd, "@IsApproved", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProductReview.IsApproved);
            else
                AddParameterToSqlCmd(sqlCmd, "@IsApproved", SqlDbType.Bit, 0, ParameterDirection.Input, System.DBNull.Value);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_CREATENEWTBLPRODUCTREVIEW);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

        }

        public static bool UpdateTblProductReview(ProductReview newTblProductReview)
        {
            // validate Parameters
            if (newTblProductReview == null)
                throw (new ArgumentNullException("newTblProductReview"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@ProductReviewId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductReview.ProductReviewId);
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductReview.ProductId);
            if (newTblProductReview.CustomerName != null)
                AddParameterToSqlCmd(sqlCmd, "@CustomerName", SqlDbType.NVarChar, 100, ParameterDirection.Input, newTblProductReview.CustomerName);
            else
                AddParameterToSqlCmd(sqlCmd, "@CustomerName", SqlDbType.NVarChar, 100, ParameterDirection.Input, System.DBNull.Value);
            if (newTblProductReview.CustomerEmail != null)
                AddParameterToSqlCmd(sqlCmd, "@CustomerEmail", SqlDbType.NVarChar, 100, ParameterDirection.Input, newTblProductReview.CustomerEmail);
            else
                AddParameterToSqlCmd(sqlCmd, "@CustomerEmail", SqlDbType.NVarChar, 100, ParameterDirection.Input, System.DBNull.Value);
            if (newTblProductReview.RatingValue != null)
                AddParameterToSqlCmd(sqlCmd, "@RatingValue", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProductReview.RatingValue);
            else
                AddParameterToSqlCmd(sqlCmd, "@RatingValue", SqlDbType.Decimal, 9, ParameterDirection.Input, System.DBNull.Value);
            if (newTblProductReview.ReviewText != null)
                AddParameterToSqlCmd(sqlCmd, "@ReviewText", SqlDbType.NVarChar, -1, ParameterDirection.Input, newTblProductReview.ReviewText);
            else
                AddParameterToSqlCmd(sqlCmd, "@ReviewText", SqlDbType.NVarChar, -1, ParameterDirection.Input, System.DBNull.Value);
            if (newTblProductReview.ReviewDate != null)
                AddParameterToSqlCmd(sqlCmd, "@ReviewDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newTblProductReview.ReviewDate);
            else
                AddParameterToSqlCmd(sqlCmd, "@ReviewDate", SqlDbType.DateTime, 0, ParameterDirection.Input, System.DBNull.Value);
            if (newTblProductReview.IsApproved != null)
                AddParameterToSqlCmd(sqlCmd, "@IsApproved", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProductReview.IsApproved);
            else
                AddParameterToSqlCmd(sqlCmd, "@IsApproved", SqlDbType.Bit, 0, ParameterDirection.Input, System.DBNull.Value);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTREVIEW_UPDATETBLPRODUCTREVIEW);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        public static IList GenerateTblProductReviewCollectionFromReader(IDataReader returnData)
        {

            //creating the instance of Employee collection
            List<ProductReview> colTblProductReview = new List<ProductReview>();

            Boolean? null_Boolean = null;
            Int32? null_Int32 = null;
            Int16? null_Int16 = null;
            Decimal? null_Decimal = null;
            DateTime? null_DateTime = null;
            Int64? null_Int64 = null;
            Double? null_Double = null;
            while (returnData.Read())
            {
                //passing the TblProductReview constructor parameters from the current instance of data reader fields.
                ProductReview newTblProductReview = new ProductReview()
                {
                    ProductReviewId = returnData["ProductReviewId"] == System.DBNull.Value ? GetProductReviewIdMinValue : (Int32)returnData["ProductReviewId"],
                    ProductId = returnData["ProductId"] == System.DBNull.Value ? 0 : (Int32)returnData["ProductId"],
                    CustomerName = returnData["CustomerName"] == System.DBNull.Value ? string.Empty : (String)returnData["CustomerName"],
                    CustomerEmail = returnData["CustomerEmail"] == System.DBNull.Value ? string.Empty : (String)returnData["CustomerEmail"],
                    RatingValue = returnData["RatingValue"] == System.DBNull.Value ? null_Decimal : (Decimal)returnData["RatingValue"],
                    ReviewText = returnData["ReviewText"] == System.DBNull.Value ? string.Empty : (String)returnData["ReviewText"],
                    ReviewDate = returnData["ReviewDate"] == System.DBNull.Value ? null_DateTime : (DateTime)returnData["ReviewDate"],
                    IsApproved = returnData["IsApproved"] == System.DBNull.Value ? (bool?)null : (bool?)returnData["IsApproved"],
                    ProductName = returnData["ProductName"] == System.DBNull.Value ? string.Empty : (String)returnData["ProductName"]

                };
                //adding the Employee to the collection
                colTblProductReview.Add(newTblProductReview);
            }

            //returns the collection of Companys 
            return (colTblProductReview);
        }

        public static Int32 GetProductReviewIdMinValue
        {
            get
            {
                return 0;
            }
        }
        public static Int32 GetProductIdMinValue
        {
            get
            {
                return 0;
            }
        }

        //create method related contants
        internal const string SPR_TBLPRODUCTREVIEW_CREATENEWTBLPRODUCTREVIEW = "spr_TblProductReview_CreateNewTblProductReview";
        internal const string SPR_TBLPRODUCTREVIEW_UPDATETBLPRODUCTREVIEW = "spr_TblProductReview_UpdateTblProductReview";
        internal const string SPR_TBLPRODUCTREVIEW_DELETETBLPRODUCTREVIEW = "spr_TblProductReview_DeleteTblProductReview";
        internal const string SPR_TBLPRODUCTREVIEW_DELETETBLPRODUCTREVIEWLIST = "spr_TblProductReview_DeleteTblProductReviewList";
        internal const string SPR_TBLPRODUCTREVIEW_GETTBLPRODUCTREVIEWBYPRODUCTREVIEWID = "spr_TblProductReview_GetTblProductReviewByProductReviewId";
        internal const string SPR_TBLPRODUCTREVIEW_GETTBLPRODUCTREVIEWBYPRODUCTID = "spr_TblProductReview_GetTblProductReviewByProductId";
        internal const string SPR_TBLPRODUCTREVIEW_GETTBLPRODUCTREVIEWBYPRODUCTIDPAGED = "spr_TblProductReview_GetTblProductReviewByProductIdPaged";
        internal const string SPR_TBLPRODUCTREVIEW_GETTBLPRODUCTREVIEWBYPRODUCTIDPAGEDCOUNT = "spr_TblProductReview_GetTblProductReviewByProductIdPagedCount";
        internal const string SPR_TBLPRODUCTREVIEW_GETALLTBLPRODUCTREVIEW = "spr_TblProductReview_GetAllTblProductReview";
        internal const string SPR_TBLPRODUCTREVIEW_GETALLTBLPRODUCTREVIEWPAGED = "spr_TblProductReview_GetAllTblProductReviewPaged";
        internal const string SPR_TBLPRODUCTREVIEW_GETALLTBLPRODUCTREVIEWPAGEDCOUNT = "spr_TblProductReview_GetAllTblProductReviewPagedCount";

        public static List<ProductReview> GetAllProductReviewsWithProductPaged(string orderBy, int startRowIndex, int maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "ProductReviewId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProductReview_GetAllProductReviewsWithProductPaged");

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductReviewCollectionFromReader);
            List<ProductReview> objCollection = ((List<ProductReview>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllProductReviewsWithProductPagedCount()
        {
            return ProductReviewDAO.GetAllTblProductReviewPagedCount("", 0, 0);
        }

        public static List<ProductReview> GetAllApprovedProductReviewsPaged(string orderBy, int startRowIndex, int maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "ProductReviewId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProductReview_GetAllApprovedTblProductReviewsPaged");

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductReviewCollectionFromReader);
            List<ProductReview> objCollection = ((List<ProductReview>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllApprovedProductReviewsPagedCount()
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProductReview_GetAllApprovedTblProductReviewsPagedCount");
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }
    }
}
